ALTER TYPE
ALTER TYPE — Change the definition of a type
Synopsis
ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER TYPE name RENAME TO new_name
ALTER TYPE name SET SCHEMA new_schema
ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ]
ALTER TYPE name action [, ... ]
ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]
ALTER TYPE name RENAME VALUE existing_enum_value TO new_enum_value
ALTER TYPE name SET ( property = value [, ... ] )
where action is one of:
ADD ATTRIBUTE attribute_name data_type [ COLLATE collation ] [ CASCADE | RESTRICT ]
DROP ATTRIBUTE [ IF EXISTS ] attribute_name [ CASCADE | RESTRICT ]
ALTER ATTRIBUTE attribute_name [ SET DATA ] TYPE data_type
[ COLLATE collation ] [ CASCADE | RESTRICT ]
Description
ALTER TYPE changes the definition of an existing type. It has several forms:
OWNER
This form changes the owner of the type.
RENAME
This form changes the name of the type.
SET SCHEMA
This form moves the type to another schema.
RENAME ATTRIBUTE
This form is only available for composite types. It changes the name of a single attribute of the type.
ADD ATTRIBUTE
This form adds a new attribute to a composite type, using the same syntax as CREATE TYPE.
DROP ATTRIBUTE [ IF EXISTS ]
This form drops an attribute from a composite type. If IF EXISTS is specified and the attribute does not exist, no error is raised. A notice is issued in this case.
ALTER ATTRIBUTE ... SET DATA TYPE
This form changes the type of an attribute of a composite type.
ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]
This form adds a new value to an enum type. The position of the new value in the enum sort order can be specified using BEFORE or AFTER an existing value. Otherwise, the new item is added at the end of the value list. If IF NOT EXISTS is specified, no error occurs if the type already contains the new value: a notice is issued but no other action is taken. Otherwise, an error occurs if the new value already exists.
RENAME VALUE
This form renames a value of an enum type. The position of the value in the enum sort order is not affected. An error occurs if the specified value does not exist or if the new name already exists.
SET ( property = value [, ... ] )
This form only applies to base types. It allows adjusting a subset of the base type properties that can be set in CREATE TYPE. Specifically, the following properties can be changed:
• RECEIVE can be set to the name of a binary input function, or to NONE to remove the type's binary input function. Using this option requires superuser privileges.
• SEND can be set to the name of a binary output function, or to NONE to remove the type's binary output function. Using this option requires superuser privileges.
• TYPMOD_IN can be set to the name of a type modifier input function, or to NONE to remove the type's type modifier input function. Using this option requires superuser privileges.
• TYPMOD_OUT can be set to the name of a type modifier output function, or to NONE to remove the type's type modifier output function. Using this option requires superuser privileges.
• ANALYZE can be set to the name of a type-specific statistics collection function, or to NONE to remove the type's statistics collection function. Using this option requires superuser privileges.
• STORAGE can be set to plain, extended, external, or main. However, changing from plain to another setting requires superuser privileges (because it requires all C functions of the type to be TOAST-ready), while changing from another setting to plain is not fully allowed (because the type may already contain TOASTed values in the database). Note that changing this option itself does not change any stored data; it only sets the default TOAST strategy for future table column creation. See ALTER TABLE for changing the TOAST strategy of existing table columns.
For more details on these type properties, see CREATE TYPE. Note that changes to these properties of a base type will automatically propagate to domains based on that type where appropriate.
ADD ATTRIBUTE, DROP ATTRIBUTE, and ALTER ATTRIBUTE actions can be combined into a list of multiple modifications to be applied in parallel. For example, you can add multiple attributes and/or change the types of multiple attributes in a single command. To use ALTER TYPE, you must own the type. To change the schema of a type, you must also have the CREATE privilege on the new schema. To change the owner, you must also be a direct or indirect member of the new owning role, and that role must have the CREATE privilege on the type's schema (these restrictions enforce that changing the owner cannot do anything that could not be accomplished by dropping and recreating the type. However, a superuser can always change the ownership of any type.).
To add an attribute or change an attribute type, you must also have the USAGE privilege on the attribute's data type.
Parameters
name
The name of an existing type to be altered (may be schema-qualified).
new_name
The new name for the type.
new_owner
The user name of the new owner of the type.
new_schema
The new schema for the type.
attribute_name
The name of the attribute to be added, altered, or dropped.
new_attribute_name
The new name of the attribute to be renamed.
data_type
The data type of the attribute to be added, or the new type of the attribute to be altered.
new_enum_value
The new value to be added to the value list of an enum type, or the new name to be assigned to an existing value. Like all enum literals, it must be quoted.
neighbor_enum_value
An existing enum value that the new value should be added immediately before or after. Like all enum literals, it must be quoted.
existing_enum_value
The existing enum value that should be renamed. Like all enum literals, it must be quoted.
property
The name of the base type property to be modified; see above for possible values.
CASCADE
Automatically propagate the operation to typed tables of the type being altered and their descendants.
RESTRICT
Refuse the operation if the type being altered is the type of a typed table. This is the default.
Notes: If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed within a transaction block, the new value cannot be used until the transaction is committed.
Comparisons involving a newly added enum value are sometimes slower than comparisons involving only the original enum values. This typically only occurs when BEFORE or AFTER is used to set the sort position of the new value to a location other than the end of the list. However, sometimes this can also occur when the new value is added at the end (if the OID counter has "wrapped around" since the enum type was created). This slowdown is usually not noticeable, but if it does cause problems, optimal performance can be restored by dropping and recreating the enum type or by dumping and reloading the entire database.
Examples
# To rename a data type:
ALTER TYPE electronic_mail RENAME TO email;
# Change the owner of type email to joe:
ALTER TYPE email OWNER TO joe;
# Change the schema of type email to customers:
ALTER TYPE email SET SCHEMA customers;
# Add a new attribute to a composite type:
ALTER TYPE compfoo ADD ATTRIBUTE f3 int;
# Add a new value to an enum type at a specific sort position:
ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
# Rename an enum value:
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
# To create binary I/O functions for an existing base type:
CREATE FUNCTION mytypesend(mytype) RETURNS bytea ...;
CREATE FUNCTION mytyperecv(internal, oid, integer) RETURNS mytype ...;
ALTER TYPE mytype SET (
SEND = mytypesend,
RECEIVE = mytyperecv );